IRIS Export (Main Roads WA)
Introduction
Councils in Western Australia are obligated to provide Main Roads WA (MRWA) with an export of their road network in a specific format for upload into the MRWA Integrated Road Information System (IRIS).
This article outlines the following:
- preparation of data in Assetic to support IRIS requirements
- field mapping
- lookup values and user friendly lookup labels
- generated data
- use of an MS Excel based tool to extract the relevant road data from Assetic for translating into the IRIS format.
This article expects the user to be familiar with the IRIS specification.
Required Data in Assetic
The IRIS export format requires information at levels equivalent to the following Assetic data structures:
- Functional Location
- Road Asset Category
- Intersection Asset Category
- Pathways Asset Category
It is therefore necessary to create Road and Intersection assets, along with Functional Locations to which the road assets can be assigned. Note that the Assetic IRIS export process does not need the asset to be linked to the Functional Location, just that the functional location exists with the same IRIS 'ROAD_NO' field.
Functional Location
The following table outlines the Functional Location fields used by the IRIS export process. These values are primarily used in the IRIS 'Road' file, but 'ROAD_NO' is a foreign key in the 'Element' and 'Inventory' files.
| Assetic Label | Assetic Field | IRIS Field | Comment |
| Functional Location Id L1 | GroupAssetIdL1 | ROAD_NO | Ensure autoID generationid for Functional Location 1 is disabled, or set autoID to use the LG_NUMBER designated by MRWA as the prefix, and pad to 7 characters |
| Functional Location Name L1 | GroupAssetNameL1 | ROAD_NAME | |
| Road Start Node | GroupAssetRoadStartNode | START_TERMINUS | The name of the start street |
| Road End Node | GroupAssetRoadEndNode | END_TERMINUS | The name of the end street |
Intersection Asset Category
The following table outlines the Intersection Asset Category fields used by the IRIS export process.
| Assetic Label | Assetic Field | IRIS Field | Comment |
| Asset Id | ComplexAssetId | NODE_NO | Not required in report but used by road asset to reference to intersection |
| AssetName | ComplexAssetName | NODE_DESCRIPTION | |
| Easting | EastingGPS | EASTING | |
| Northing | NorthingGPS | NORTHING | |
| External Id | ComplexAssetExternalIdentifier | LG_NODE_ID | Needs to be a number |
| Road Authority Intersection Number | RoadAuthIntnum | IRIS_NODE_ID | Optionally record the MRWA IRIS ID |
Note that "Asset Id" could also be used for the IRIS "LG_NODE_ID" in place of the Assetic attribute "External Id", however "Asset ID" would need to be a number.
Road Asset Category
The following table outlines the fields in Assetic Road Category that are used by the IRIS export.
| Assetic Label | Assetic Field | IRIS Field | Comment |
| Element Type | ElementType | ELEMENT_TYPE | Use either "D" or "S" in Assetic. If null will assume "S" |
| Start Chainage | StartChainage | METRES_START (element, inventory) |
Use 0 if start of road. If NULL then the export process will use End Chainage and Segment Length to calculate Start Chainage. If End Chainage s null or zero then asset is excluded from the report |
| End Chainage | EndChainage |
METRES_END (inventory) |
The End Chainage is also used to determine the ELEMENT_SEQUENCE_NUMBER in the IRIS 'element' export. This is achieved by grouping the elements by ROAD_NO and sorting by End Chainage. |
| Segment Length | SegmentLength |
LENGTH |
No transformation applied |
| Road Start Node | RoadStartNode | START_LG_NODE_ID | Asset Id of the Intersection asset that the road starts at |
| Road End Node | RoadEndNode | END_LG_NODE_ID | Asset Id of the Intersection asset that the road end at |
| Carriageway | IRIS Carriageway Code | CARRIAGEWAY | Carriageway is entered in Assetic with the label consisting of a user friendly label and the IRIS value in braces. Export process extracts and uses the value within the braces |
| IRIS Hierarchy | IRIShierarchy | ROAD_HIERARCHY | IRIS Hierarchy is entered in Assetic with user friendly labels. The export process translates the label to the IRIS code. |
| Date of Construction | ConstructionDate | PAVE_YEAR | The year component of the Date of Construction is used for pavement year |
| Original Surface Year | OrigResealYr | ORIG_SURF_YEAR | No transformation applied |
| Original Surface Type | OrigsurfaceType | SURF_TYPE | Original Surface Type is entered in Assetic with user friendly labels. The export process translates the label to the IRIS code. |
| Reseal Year | ResealYrOne | RESEAL_YEAR1 | No transformation applied |
| Reseal Year 2 | ResealYrTwo | RESEAL_YEAR2 | No transformation applied |
| Drainage Left | DrainageLeft | DRAINAGE_LEFT | No transformation applied |
| Drainage Right | DrainageRight | DRAINAGE_RIGHT | No transformation applied |
| AADT | AADT | TRAFFIC_COUNT | No transformation applied |
| Traffic Count Date | TrafficCountDate | TRAFFIC_YEAR | The year component of Traffic Count Date is used |
| General Terrain | Terrain | GENERAL_TERRAIN | No transformation applied |
| Special Use | SpecialUse | SPECIAL_USE | Special Use is entered in Assetic with the label consisting of a user friendly label and the IRIS value in braces. Export process extracts and use the value within the braces |
| Surface Treatment | SurfaceTreat | SURF_TREATMENT | Surface Treatment is entered in Assetic with user friendly labels. The export process translates the label to the IRIS code. |
| Treatment Year | UstreatmentYr | TREATMENT_YEAR | No transformation applied |
| Cross Section Type | Xsection | XSECT_TYPE | Cross Section Type is entered in Assetic with the label consisting of a user friendly label and the IRIS value in braces. Export process extracts and use the value within the braces |
| Pavement Base Width | PavementWidth | PAVE_WIDTH | No transformation applied |
| Current Surface Type | SurfaceTypeIRIS | LATEST_RESEAL_TYPE | Current Surface Type is entered in Assetic with user friendly labels. The export process translates the label to the IRIS code. |
| Surface Width | SurfaceWidth | SURF_WIDTH | No transformation applied |
| Formation Width | RoadFormationWidth | FORM_WIDTH | No transformation applied |
| Speed Zone | SpeedZone | SPEED_LIMIT | No transformation applied |
| - | - | EXIT_LEG_NO | Hardcoded as null |
| - | - | APPROACH_LEG_NO | Hardcoded as null |
Pathway Asset Category
The following table outlines the fields in Assetic Road Category that are used by the IRIS export.
| Assetic Label | Assetic Field | IRIS Field | Comment |
| Asset Type | ComplexAssetType | PATH_TYPE | In Assetic use a descriptive label for the type, the IRIS code is derived via lookup in the report query. |
| Path Location | PathLocation | Use one of "ROAD" or "OTHER" | |
| Path Use | PathUse | Use one of "DUP" (Dual Use Path) or "POP" (Pedestrain Only Path) | |
| Area | Area |
AREA_OF_POP_ROAD AREA_OF_DUP_ROAD AREA_OF_POP_OTHER AREA_OF_DUP_OTHER |
Area is summed and grouped by PathLocation and PathUse |
| Length | Area |
LENGTH_OF_DUP_ROAD LENGTH_OF_DUP_ROAD LENGTH_OF_POP_OTHER LENGTH_OF_DUP_OTHER |
Length is summed and grouped by PathLocation and PathUse |
Running Export Process
The following steps should be followed the accomplish the creation of an IRIS formatted extract from Assetic.
1. Refresh Data
1. In the Excel spreadsheet go to the "Data" tab and use the button "Refresh All". This ensures the data in the spreadsheet is current.
/1_mceclip6.png)
2. Verify Data
There are sheets in the Excel spreadsheet that show the un-formatted data used to prepare the export sheets. These sheets make it easier to view the data.
| Worksheet | Description |
| LocationPrep | This is the data used for the IRIS_road export file. The functional location is used to define the roads. |
| Intersection_Prep | This is the data used for the IRIS_node export file. The asset category 'Intersections' is the data source. |
| RawRoadData | This is the asset data for the asset category 'Roads'. It is the data directly from Assetic without any modification or manipulation |
| Element_Prep | The raw road data is manipulated to match the IRIS requirements for the IRIS_element export file. |
| Inventory_Prep | The raw road data is manipulated to match the IRIS requirements for the IRIS_inventory export file. |
| RoadsWKT | The spatial data in WKT format needs to be prepared using GIS tools external to the spreadsheet. Paste the WKT definition for each asset in this worksheet. |
| MissingChainage | Assets in this worksheet are missing chainage information and cannot be included in the IRIS export. Define a start and end chainage for these assets and refresh the report. |
Verify Asset Count
- The number of records returned in the sheet "RawRoadData" and "ElementPrep" should be the same as the total number of assets in the Road asset category that are owned by council.
- The number of records returned in the sheet "201_node" should be the same as the total number of assets in the Intersections asset category that are owned by council.
- The number of records returned in the sheet "201_road" should be the same as the number of council roads
3. Save export sheets to files
The following sheets are saved as 'txt' files for sending to MRWA
| Worksheet | Save As | Comment |
| IRIS_database | <LG>_database.txt | The values in this sheet need to be manually entered |
| IRIS_road | <LG>_road.txt | |
| IRIS_Element | <LG>_element.txt | |
| IRIS_node | <LG>_node.txt | |
| IRIS_Inv | <LG>_inv.txt | This worksheet is built using a pivot table because the inventory is comprised of aggregated road elements. The pivot is used to achieve this aggregation. |
| IRIS_Path | <LG>_path.txt |
Save each 'export sheet' one at a time using the Excel 'Save As' feature. Change the file type from Excel Workbook to 'Text ()Tab Delimited'. Replace "<LG>" with the number assigned to you by MRWA.
Note that each 'export sheet' has a single column. This column already contains the data delimited by a pipe ("|") as per the IRIS specification.
/2_mceclip8.png)
Depending on your Excel environment it may be necessary to open each 'txt' file in Notepad and bulk replace double quotation marks inserted by Excel at the start and end of each line.
Configuring the connection to Assetic
Change OData Source
You may need to change the Assetic OData source for your report. Reasons for this may include:
- You are using the sample Excel Spreadsheet or sample Power BI report attached to this article. These reports can be used with your own Assetic data once you change the query source to point to your environment.
- You have initially created the report against you Sandbox data and now want to report against your Production data.
First list the connections Excel uses to connect to Assetic. In the 'Data' menu of Excel, click on 'Queries & Connections'
/3_mceclip0.png)
Next right-click on the query "GetRoadData" and click 'Edit'
/4_mceclip1.png)
The "Query Editor" dialog window will appear. Choose the menu option "Data Source Settings".
/5_mceclip13.png)
The "Data Source Settings" dialog window will open.
/6_mceclip2.png)
Highlight the OData endpoint you want to edit and then choose the "Change Source" button.
A new dialog window appears showing the current OData endpoint. Change the endpoint to your target Assetic environment. Once you have made the change choose "OK"
/7_mceclip3.png)
The report will need to be refreshed to load the data from your new environment, but you will first need to reset the authentication credentials to match your new data source.
Resetting Authentication Credentials
If your credentials are not valid you will need to reset them. They may be for several reasons including:
- You have changed your Assetic password
- You have changed the report data source (from Sandbox to Production)
To change your credentials first open the "Data Source Settings" dialog window (as shown in the section Change Odata Source).
Highlight the OData endpoint you want to edit and then choose the "Edit Permissions" button.
/8_mceclip2.png)
A new "Edit Permissions" dialog window appears. Choose the "Edit..." button in the "Credentials" section of the dialog window.
/9_mceclip4.png)
The same authentication dialog window that was used when the query was first created appears. Apply the changes and save.
/10_mceclip5.png)
How it Works
Assetic OData Endpoint
The export process connects to Assetic via the Assetic OData endpoint, which is a standardised way for Excel to access data from cloud applications such as Assetic.
For further reading about OData refer to the following Assetic Knowledge Base articles:
